SET NOCOUNT ON

-- OJO: Para ejecutarlo de preferencia correrlo como Texto y cambiar el ancho de la columna a 8000 en "Query Options" 
DECLARE @sCmdPaso VARCHAR(MAX),
		@sCmd VARCHAR(MAX),
		@sNomEsquema  VARCHAR(10),
		@sNomTabla VARCHAR(100),
		@nIdTabla INT,
		@nSustituyeCamposControl TINYINT

DECLARE @Tablas TABLE(IdTabla INT IDENTITY(1,1), NomEsquema VARCHAR(10), NomTabla VARCHAR(100))
DECLARE @TablaCampos TABLE(NomCampo VARCHAR(50), EsPK TINYINT, TipoDato INT)
DECLARE @TblResultado TABLE(Resultado VARCHAR(MAX))

SET		@nSustituyeCamposControl = 1
-- Incluir las tablas que requieren la carga inicial
--INSERT  INTO @Tablas(NomEsquema, NomTabla) VALUES('dbo', 'CatTabla')
INSERT  INTO @Tablas(NomEsquema, NomTabla) VALUES('dbo', 'CatColonia')
SELECT	@nIdTabla = MIN(IdTabla)
FROM	@Tablas

WHILE @nIdTabla IS NOT NULL
BEGIN
	DELETE @TablaCampos

	SELECT	@sNomEsquema = NomEsquema,
			@sNomTabla = NomTabla
	FROM	@Tablas
	WHERE	IdTabla = @nIdTabla

	INSERT INTO @TablaCampos
		SELECT	t2.name, CASE WHEN t4.object_id IS NOT NULL THEN 1 ELSE 0 END,
				t2.system_type_id
		FROM	sys.schemas t0
				INNER JOIN sys.objects t1 ON
					t1.schema_id = t0.schema_id
				INNER JOIN sys.columns t2 ON
					t2.object_id = t1.object_id
				LEFT JOIN sys.key_constraints t3 ON
					t3.parent_object_id = t1.object_id AND
					t3.type = 'PK'
				LEFT JOIN sys.index_columns t4 ON
					t4.object_id = t2.object_id AND
					t4.column_id = t2.column_id AND
					t4.index_id = t3.unique_index_id
		WHERE	t0.name = @sNomEsquema AND
				t1.name = @sNomTabla AND
				t1.type = 'u'
	
	IF (SELECT count(1) FROM @TablaCampos WHERE EsPK = 1) = 0
		continue

	IF @nSustituyeCamposControl = 1
	BEGIN
		UPDATE @TablaCampos SET NomCampo = '@tFechaMod' WHERE NomCampo = 'FechaMod'
		UPDATE @TablaCampos SET NomCampo = '@sIpMod' WHERE NomCampo = 'IpMod'
		UPDATE @TablaCampos SET NomCampo = '@nIdUsuarioMod' WHERE NomCampo = 'IdUsuarioMod'
	END
--SELECT * FROM sys.systypes
	SET		@sCmdPaso = ''
	SELECT	@sCmdPaso = ISNULL(@sCmdPaso,'') + ' AND '+ NomCampo + ' = ' + (CASE WHEN TipoDato IN (58,61,167,175,231,239) THEN '''''''' ELSE '''' END) + ' + CONVERT(VARCHAR(MAX),' + NomCampo + ') + ' + (CASE WHEN TipoDato IN (58,61,167,175,231,239) THEN '''''''' ELSE '''' END)
	FROM	@TablaCampos
	WHERE	EsPK = 1
	SET		@sCmdPaso = SUBSTRING(@sCmdPaso,5,LEN(@sCmdPaso)-4)

	SET		@sCmd = 'SELECT ''IF NOT EXISTS( SELECT 1 FROM ' + @sNomEsquema + '.' + @sNomTabla + ' WHERE' + @sCmdPaso + ' ) INSERT INTO ' + @sNomEsquema + '.' + @sNomTabla + '('

	SET		@sCmdPaso = ''
	SELECT	@sCmdPaso = ISNULL(@sCmdPaso,'') + ',' + REPLACE(REPLACE(REPLACE(NomCampo,'@t',''),'@s',''),'@n','')
	FROM	@TablaCampos
	set		@sCmdPaso = SUBSTRING(@sCmdPaso,2,LEN(@sCmdPaso)-1)
	SET		@sCmd = @sCmd + @sCmdPaso + ') VALUES('

	SET		@sCmdPaso = ''
	SELECT	@sCmdPaso = ISNULL(@sCmdPaso,'') + (CASE WHEN SUBSTRING(NomCampo,1,1) = '@' THEN NomCampo ELSE ( (CASE WHEN TipoDato IN (58,61,167,175,231,239) THEN '''''''' ELSE '''' END) + ' + ISNULL(LTRIM(RTRIM(CONVERT(VARCHAR(MAX),' + NomCampo + (CASE WHEN TipoDato = 61 THEN ',112' ELSE '' END) + '))),'+ (CASE WHEN TipoDato IN (58,167,175,231,239) THEN '''''' ELSE '''NULL''' END)  +') + ' + (CASE WHEN TipoDato IN (58,61,167,175,231,239) THEN '''''''' ELSE '''' END) ) END) + ','   
	FROM	@TablaCampos
	SET		@sCmdPaso = SUBSTRING(@sCmdPaso,1,LEN(@sCmdPaso)-1)

	SET		@sCmd = @sCmd + @sCmdPaso + ')'' FROM ' + @sNomEsquema + '.' + @sNomTabla

	--SELECT @sCmd
	PRINT '----------------------------------------------'
	PRINT '-- ' + @sNomEsquema + '.' + @sNomTabla
	PRINT '----------------------------------------------'

	DELETE @TblResultado

	INSERT INTO @TblResultado
		EXEC ( @sCmd )

	UPDATE @TblResultado SET Resultado = REPLACE(Resultado,'''NULL''','NULL')
	UPDATE @TblResultado SET Resultado = REPLACE(Resultado,'''''','NULL')

	SELECT * FROM @TblResultado

	SELECT	@nIdTabla = MIN(IdTabla)
	FROM	@Tablas
	WHERE	IdTabla > @nIdTabla
END